In [1]:
from IPython.core.display import display, HTML
display(HTML("""<style> .container {width:96% !important;}</style>"""))

from IPython.display import IFrame
In [2]:
import pandas as pd
import numpy as np
# from plotly.offline import init_notebook_mode, iplot
# import cufflinks as cf
# init_notebook_mode()
# cf.go_offline()
from __future__ import division
In [3]:
!ls additional_data
path = 'additional_data/'
nat03_04.csv
nat05_06.csv
nat07_08.csv
t0001-10.1080%2F10691898.2018.1434342.csv
t0002-10.1080%2F10691898.2018.1434342.csv
t0003-10.1080%2F10691898.2018.1434342.csv
t0004-10.1080%2F10691898.2018.1434342.csv
t0005-10.1080%2F10691898.2018.1434342.csv
t0006-10.1080%2F10691898.2018.1434342.csv
t0007-10.1080%2F10691898.2018.1434342.csv
t0008-10.1080%2F10691898.2018.1434342.csv
t0009-10.1080%2F10691898.2018.1434342.csv
t0010-10.1080%2F10691898.2018.1434342.csv
t0011-10.1080%2F10691898.2018.1434342.csv
t0012-10.1080%2F10691898.2018.1434342.csv
t0013-10.1080%2F10691898.2018.1434342.csv
In [4]:
nat34 = pd.read_csv(path + 'nat03_04.csv')
nat56 = pd.read_csv(path + 'nat05_06.csv')
nat78 = pd.read_csv(path + 'nat07_08.csv')
In [5]:
print nat34.shape
print nat56.shape
print nat78.shape
(126483, 27)
(153565, 27)
(111416, 27)
In [6]:
nat34.head().T
Out[6]:
0 1 2 3 4
LoanNr_ChkDgt 1005255000 1005265003 1005275006 1005285009 1005295001
Name Clark's Inc. James Byung Ho Park dba Parago Danny W. Temple and Beau M. Te J & V Enterprises, LLC Susana Chung dba Law Offices o
City VISTA DOWNEY MONTE VISTA COLORADO SPRINGS LOS ANGELES
State CA CA CO CO CA
Zip 92084 90242 81144 80906 90010
Bank CALIFORNIA BANK & TRUST CALIFORNIA BANK & TRUST CALIFORNIA BANK & TRUST CALIFORNIA BANK & TRUST CALIFORNIA BANK & TRUST
BankState CA CA CA CA CA
NAICS 442210 422990 561730 722211 541110
ApprovalDate 6-Nov-02 6-Nov-02 6-Nov-02 10-Dec-02 10-Dec-02
ApprovalFY 2003 2003 2003 2003 2003
Term 2 83 36 36 36
NoEmp 2 10 2 30 8
NewExist 1 1 1 1 1
CreateJob 0 0 0 0 0
RetainedJob 0 0 0 0 0
FranchiseCode 1 1 1 1 1
UrbanRural 0 0 2 1 0
RevLineCr Y N Y Y Y
LowDoc N N N N N
ChgOffDate 29-Mar-06 NaN NaN NaN NaN
DisbursementDate 30-Nov-02 30-Nov-02 31-Dec-02 31-Dec-02 31-Dec-02
DisbursementGross $40,000.00 $100,000.00 $25,000.00 $64,263.00 $50,000.00
BalanceGross $0.00 $0.00 $0.00 $0.00 $0.00
MIS_Status CHGOFF P I F P I F P I F P I F
ChgOffPrinGr $40,000.00 $0.00 $0.00 $0.00 $0.00
GrAppv $40,000.00 $100,000.00 $25,000.00 $50,000.00 $50,000.00
SBA_Appv $20,000.00 $50,000.00 $12,500.00 $25,000.00 $25,000.00
Variable Name LoanNr_ChkDgt: Identifier – Primary Key Name: Borrower Name City: Borrower City State: Borrower State Zip: Borrower Zip Code Bank: Bank Name BankState: Bank State NAICS: North American Industry Classification System code ApprovalDate: Date SBA Commitment Issued ApprovalFY: Fiscal Year of Commitment Term: Loan term in months NoEmp: Number of Business Employees NewExist: 1 = Existing Business, 2 = New Business CreateJob: Number of jobs created RetainedJob: Number of jobs retained FranchiseCode: Franchise Code 00000 or 00001 = No Franchise UrbanRural: 1= Urban, 2= Rural, 0 = Undefined RevLineCr: Revolving Line of Credit : Y = Yes LowDoc: LowDoc Loan Program: Y = Yes, N = No ChgOffDate: The date when a loan is declared to be in default DisbursementDate: Disbursement Date DisbursementGross: Amount Disbursed BalanceGross: Gross amount outstanding MIS_Status: Loan Status ChgOffPrinGr: Charged-off Amount GrAppv: Gross Amount of Loan Approved by Bank SBA_Appv: SBA’s Guaranteed Amount of Approved Loan
In [7]:
# Default if MIS_status = CHGOFF, else MIS_status = PIF

print nat34.MIS_Status.value_counts()

print 'Default rate =', (nat34.MIS_Status == 'CHGOFF').sum() / len(nat34)
P I F     105464
CHGOFF     20731
Name: MIS_Status, dtype: int64
Default rate = 0.163903449475
In [8]:
print (nat34.MIS_Status == 'CHGOFF').sum() / len(nat34)
print (nat56.MIS_Status == 'CHGOFF').sum() / len(nat56)
print (nat78.MIS_Status == 'CHGOFF').sum() / len(nat78)
0.163903449475
0.299521375313
0.421016730093
In [9]:
nat34.BalanceGross.value_counts()
Out[9]:
$0.00     126483
Name: BalanceGross, dtype: int64

EDA on features

In [10]:
nat34.State.value_counts().head()
Out[10]:
CA    20382
TX     9920
FL     7956
PA     7941
NY     6566
Name: State, dtype: int64
In [11]:
nat34.City.value_counts().head()
Out[11]:
LOS ANGELES    2664
NEW YORK       1529
HOUSTON        1507
MIAMI          1288
DALLAS          769
Name: City, dtype: int64
In [12]:
nat34.Zip.value_counts().head()
Out[12]:
90015    245
90010    163
90021    142
33166    136
90014    118
Name: Zip, dtype: int64
In [13]:
nat34.UrbanRural.value_counts()
Out[13]:
1    98286
2    24022
0     4175
Name: UrbanRural, dtype: int64
In [14]:
nat34.Bank.value_counts().head()
Out[14]:
BANK OF AMERICA NATL ASSOC     21192
CITIZENS BANK NATL ASSOC        9893
WELLS FARGO BANK NATL ASSOC     6566
CAPITAL ONE NATL ASSOC          6395
BBCN BANK                       5492
Name: Bank, dtype: int64
In [15]:
nat34.NAICS.value_counts().head()
Out[15]:
722110    5918
722211    5396
811111    2391
812112    2126
447110    1858
Name: NAICS, dtype: int64
In [16]:
nat34['ApprovalDate'] = pd.to_datetime(nat34.ApprovalDate)
In [17]:
nat34.ApprovalDate.dt.month.value_counts()
Out[17]:
9     12447
6     11766
3     11563
8     11455
7     11427
4     11392
5     11331
12     9733
10     9568
11     8801
2      8524
1      8476
Name: ApprovalDate, dtype: int64
In [18]:
nat34.ApprovalFY.value_counts().head()
Out[18]:
2004    68290
2003    58193
Name: ApprovalFY, dtype: int64
In [19]:
nat34.NoEmp.value_counts().head()
Out[19]:
1    25723
2    22167
3    13999
4    10661
5     8545
Name: NoEmp, dtype: int64
In [20]:
nat34.NewExist.value_counts()
Out[20]:
1.0    98731
2.0    27486
0.0      266
Name: NewExist, dtype: int64
In [21]:
nat34[nat34.MIS_Status == 'CHGOFF'].sample(10).T
Out[21]:
59902 20598 77251 102115 23766 6443 68721 23763 42851 5108
LoanNr_ChkDgt 6686534002 6039114009 7039484009 7419964002 6098664010 5762504002 6858314003 6098634001 6385504000 1066585009
Name JAVA & NEWS, LLC CUSTOM CLIMATE SOLUTIONS INC BRILEY'S COFFEE CAFE COMPANY TAJ BOUTIQUE COATING SERVICE AND CONSULTING PETLAND COUNTS & ASSOCIATES INC GMP AUTOMOTIVE MACHINE & TUNE LISA'S FLOWERS & GIFT SHOP DB Phase 1, LLC
City JACKSON GRAND RAPIDS MURPHY NEW LONDON WESTFIELD TOPSHAM (CENSUS NAME FOR TOPSH FORT WORTH SANTA CRUZ WEST ALLIS DELRAY BEACH
State MS MI TX CT MA ME TX CA WI FL
Zip 39283 49546 75094 6320 1085 4086 76117 95060 53214 33484
Bank STATE BANK & TRUST COMPANY PNC BANK, NATIONAL ASSOCIATION WELLS FARGO BANK NATL ASSOC CITIZENS BANK NATL ASSOC CITIZENS BANK NATL ASSOC KEYBANK NATIONAL ASSOCIATION CAPITAL ONE NATL ASSOC BANK OF AMERICA NATL ASSOC U.S. BANK NATIONAL ASSOCIATION BANK OF AMERICA NATL ASSOC
BankState MS DE SD RI RI OH VA NC OH NC
NAICS 722211 235110 722110 448140 541611 453910 238210 811111 453110 531311
ApprovalDate 2003-09-15 00:00:00 2003-01-22 00:00:00 2003-12-16 00:00:00 2004-05-07 00:00:00 2003-02-13 00:00:00 2002-10-07 00:00:00 2003-10-30 00:00:00 2003-02-13 00:00:00 2003-06-06 00:00:00 2004-09-22 00:00:00
ApprovalFY 2003 2003 2004 2004 2003 2003 2004 2003 2003 2004
Term 1 49 61 38 14 47 37 1 60 44
NoEmp 6 2 2 2 1 1 30 5 4 7
NewExist 2 2 1 1 1 2 1 1 1 2
CreateJob 0 0 10 0 0 14 10 0 1 0
RetainedJob 0 2 12 2 1 1 30 5 5 0
FranchiseCode 1 1 1 1 1 63810 1 1 1 1
UrbanRural 1 1 1 1 1 2 1 1 1 2
RevLineCr 0 0 0 Y Y 0 0 N Y Y
LowDoc Y N N N N N N N N N
ChgOffDate 9-Nov-10 20-Jan-05 14-Mar-06 12-May-08 7-Mar-09 4-Nov-10 22-Mar-08 24-Jan-11 10-Aug-05 12-May-08
DisbursementDate 13-Jun-04 31-Mar-03 29-Feb-04 31-May-04 31-Mar-03 31-Jan-03 31-Dec-03 31-May-03 30-Jun-03 31-Oct-04
DisbursementGross $66,000.00 $30,000.00 $167,292.00 $30,000.00 $61,733.00 $360,700.00 $50,000.00 $50,000.00 $23,664.00 $100,180.00
BalanceGross $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
MIS_Status CHGOFF CHGOFF CHGOFF CHGOFF CHGOFF CHGOFF CHGOFF CHGOFF CHGOFF CHGOFF
ChgOffPrinGr $34,495.00 $27,860.00 $127,755.00 $12,227.00 $39,779.00 $3,636.00 $43,609.00 $4,136.00 $23,664.00 $49,840.00
GrAppv $66,000.00 $30,000.00 $195,000.00 $30,000.00 $40,000.00 $452,000.00 $50,000.00 $65,000.00 $25,000.00 $50,000.00
SBA_Appv $56,100.00 $15,000.00 $146,250.00 $15,000.00 $20,000.00 $339,000.00 $25,000.00 $32,500.00 $12,500.00 $25,000.00
In [ ]:
nat34[nat34.MIS_Status == 'CHGOFF'].City.value_counts().head(10).iplot(kind = 'bar')
In [29]:
nat34[nat34.MIS_Status == 'CHGOFF'].Zip.value_counts().head(10)
Out[29]:
90010    70
90015    56
33166    48
90014    43
90006    36
90021    30
90005    27
90020    27
10001    23
33012    22
Name: Zip, dtype: int64
In [32]:
nat34[nat34.MIS_Status == 'CHGOFF'].Zip.astype(str).str[:3].value_counts().head(10)
Out[32]:
900    654
331    450
770    396
750    330
330    257
300    211
917    190
752    186
207    186
100    174
Name: Zip, dtype: int64

In [35]:
nat34[nat34.MIS_Status == 'CHGOFF'].City.astype(str).str[:3].value_counts().head(10)
Out[35]:
LOS    689
SAN    655
NEW    542
MIA    408
HOU    402
NOR    264
FOR    253
WES    249
BRO    213
DAL    206
Name: City, dtype: int64
In [37]:
nat34[nat34.MIS_Status == 'CHGOFF'].Bank.value_counts().head()
Out[37]:
BANK OF AMERICA NATL ASSOC     4651
BBCN BANK                      2624
CAPITAL ONE NATL ASSOC         1945
CITIZENS BANK NATL ASSOC       1356
WELLS FARGO BANK NATL ASSOC     914
Name: Bank, dtype: int64
In [38]:
nat34.UrbanRural.value_counts()
Out[38]:
1    98286
2    24022
0     4175
Name: UrbanRural, dtype: int64
In [39]:
nat34.RevLineCr.value_counts()
Out[39]:
0    60080
Y    42526
N    19891
T     3962
R        1
1        1
A        1
Name: RevLineCr, dtype: int64
In [ ]:
train[train.default == 1].Term.iplot(kind = 'hist')
In [ ]:
train[train.default == 0].Term.iplot(kind = 'hist')